package com.sky.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface ReportMapper {

    /**
     * 根据日期查询营业额
     *
     * @param date
     * @param status
     * @return
     */
    @Select("select sum(amount) from orders where status=#{status} and DATE_FORMAT(order_time,'%Y-%m-%d')=#{date}")
    Long getAmountByDate(LocalDate date, Integer status);

    /**
     * 根据日期查询新用户数
     *
     * @param date
     * @return
     */
    @Select("select count(id) from user where DATE_FORMAT(create_time,'%Y-%m-%d')=#{date}")
    Long getUserByDate(LocalDate date);

    /**
     * 根据日期查询总用户数
     *
     * @param begin
     * @return
     */
    @Select("select count(id) from user where DATE_FORMAT(create_time,'%Y-%m-%d')<=#{date}")
    Long getTotalUserByDate(LocalDate begin);

    /**
     * 查询总订单数
     *
     * @param date
     * @return
     */
    @Select("select count(id) from orders where DATE_FORMAT(order_time,'%Y-%m-%d')=#{date}")
    Long orderCount(LocalDate date);

    /**
     * 查询有效订单数
     *
     * @param date
     * @param status
     * @return
     */
    @Select("select count(id) from orders where status=#{status} and DATE_FORMAT(order_time,'%Y-%m-%d')=#{date}")
    Long validOrderCount(LocalDate date, Integer status);

    @Select("select od.name from order_detail od left join sky_take_out.orders o on od.order_id = o.id where DATE_FORMAT(o.order_time,'%Y-%m-%d')<=#{end} and DATE_FORMAT(o.order_time,'%Y-%m-%d')>=#{begin} and o.status=5" +
            " group by od.name order by sum(od.number) desc limit 0,10")
    List<String> getName(LocalDate begin, LocalDate end);

    @Select("select sum(od.number) from order_detail od left join sky_take_out.orders o on od.order_id = o.id" +
            " where DATE_FORMAT(order_time,'%Y-%m-%d')<=#{end} and DATE_FORMAT(order_time,'%Y-%m-%d')>=#{begin} " +
            "and o.status=5 group by od.name order by sum(od.number) desc limit 0,10")
    List<Integer> getNumber(LocalDate begin, LocalDate end);
}
